package com.example.drcbackend.controller;

import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLColumnDefinition;
import com.alibaba.druid.sql.ast.statement.SQLCreateTableStatement;
import com.alibaba.druid.sql.ast.statement.SQLTableElement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.example.drcbackend.Mapper.*;
import com.example.drcbackend.entity.*;
import com.example.drcbackend.entity.dto.TableSchema;
import com.example.drcbackend.jwt.Result;
import com.example.drcbackend.util.SqlParserUtil;
import org.apache.ibatis.annotations.Mapper;
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.NoSuchFileException;
import java.nio.file.Paths;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@RequestMapping("/db")
@RestController
@CrossOrigin
public class DbController {
    @Autowired
    private DbMapper dm;


    @Autowired
    private attributessetsMapper atm;
    @Autowired
    private functionaldependenciesMapper fdm;
    @Autowired
    private SubsetdbMapper sm;


    @PostMapping("/alldb")//显示所有数据库
    public Result alldb() {
        LambdaQueryWrapper<Db> lqw = new LambdaQueryWrapper<>();
        List<Db> db=dm.selectList(lqw);
        System.out.println("看看数据库数据");
        System.out.println(db);
        return Result.ok().data("db",db);
    }

    @PostMapping("/onedb")//显示数据库的所有数据
    public Result onedb(String DBid) {
        LambdaQueryWrapper<Db> lqw = new LambdaQueryWrapper<>();
        lqw.eq(Db::getDbid,DBid);
        Db db=dm.selectOne(lqw);


        return Result.ok().data("db",db);
    }

    @PostMapping("/allindb")
    public Result allindb(){// 获取所有不一致数据库
        LambdaQueryWrapper<Db> lqw = new LambdaQueryWrapper<>();
        List<Db> db=dm.selectList(lqw);
        List<Db> incondb= new ArrayList<>();
        for(Db dbs:db){
            if(dbs.getConsistency()==0)
                incondb.add(dbs);
        }
        return Result.ok().data("db",incondb);
    }

    @PostMapping("/createcondb")//新建一致数据库
    public Result createcondb(String DBid,String databaseName) {
        Db newdb = new Db();
        newdb.setDbname(databaseName);
        LambdaQueryWrapper<Db> lqw = new LambdaQueryWrapper<>();
        lqw.eq(Db::getDbid,DBid);
        Db db=dm.selectOne(lqw);

        newdb.setConsistency(1);
        //初始先将数据库一致性设为1，即为一致数据库实例

        dm.insert(newdb);
        System.out.println(newdb);
        return Result.ok().data("dbid",newdb.getDbid());
    }

    @PostMapping("/dbdetails")// 显示数据库具体信息
    public Result dbdetails(String DBid) {
        LambdaQueryWrapper<Db> lqw = new LambdaQueryWrapper<>();
        lqw.eq(Db::getDbid,DBid);
        Db db=dm.selectOne(lqw);
        String dbname=db.getDbname();// 数据库名称，这里其实就是表的名称
        LambdaQueryWrapper<attributessets> lqwst = new LambdaQueryWrapper<>();
        lqwst.eq(attributessets::getDbid,DBid);
        List<attributessets> at=atm.selectList(lqwst);// 属性集
        LambdaQueryWrapper<functionaldependencies> lqwfd = new LambdaQueryWrapper<>();
        lqwfd.eq(functionaldependencies::getDbid,DBid);
        List<functionaldependencies> fd = fdm.selectList(lqwfd);// 函数依赖集
        System.out.println(at);
        System.out.println(fd);
        System.out.println("tree");

        return Result.ok().data("dbname",dbname).data("at",at).data("fd",fd);
    }

    // 新增方法：根据数据库ID解析SQL文件并返回表格数据
    // 在DbController类中添加以下方法
    @PostMapping("/showinstance")
    public Result showInstance(String DBid) {
        String filePath = "E:/SQL_test/" + DBid + ".sql";
        try {
            // 读取SQL文件内容
            String sqlContent = new String(Files.readAllBytes(Paths.get(filePath)), StandardCharsets.UTF_8);

            // 使用SQL解析工具获取表结构
            List<TableSchema> schemas = SqlParserUtil.extractAllSchemas(sqlContent);
            if (schemas.isEmpty()) {
                return Result.error().message("未找到表结构信息");
            }

            // 使用Druid解析器获取完整列信息
            List<Map<String, String>> columns = new ArrayList<>();
            SQLStatementParser parser = new MySqlStatementParser(sqlContent);
            List<SQLStatement> statements = parser.parseStatementList();

            for (SQLStatement stmt : statements) {
                if (stmt instanceof SQLCreateTableStatement) {
                    SQLCreateTableStatement createStmt = (SQLCreateTableStatement) stmt;
                    for (SQLTableElement element : createStmt.getTableElementList()) {
                        if (element instanceof SQLColumnDefinition) {
                            SQLColumnDefinition colDef = (SQLColumnDefinition) element;
                            Map<String, String> columnInfo = new LinkedHashMap<>();
                            // 获取列名
                            String colName = colDef.getName().getSimpleName();
                            // 获取列类型（包含长度信息）
                            String colType = colDef.getDataType().getName();
                            if (colDef.getDataType().getArguments() != null) {
                                colType += colDef.getDataType().getArguments().toString()
                                        .replace("[", "(")
                                        .replace("]", ")")
                                        .replace(", ", ",");
                            }

                            columnInfo.put("name", colName);
                            columnInfo.put("type", colType);
                            columns.add(columnInfo);
                        }
                    }
                    break; // 只处理第一个表
                }
            }

            // 解析插入数据
            List<Map<String, Object>> data = new ArrayList<>();
            Pattern insertPattern = Pattern.compile(
                    "INSERT INTO\\s+`?(\\w+)`?\\s*\\((.*?)\\)\\s*VALUES\\s*(\\(.*?\\));",
                    Pattern.CASE_INSENSITIVE | Pattern.DOTALL
            );

            Matcher matcher = insertPattern.matcher(sqlContent);
            while (matcher.find()) {
                String[] keys = matcher.group(2).split("\\s*,\\s*");
                String valuesStr = matcher.group(3).replaceAll("[()]", "");

                List<String> values = new ArrayList<>();
                boolean inQuotes = false;
                StringBuilder currentValue = new StringBuilder();

                for (char c : valuesStr.toCharArray()) {
                    if (c == '\'') {
                        inQuotes = !inQuotes;
                    } else if (c == ',' && !inQuotes) {
                        values.add(currentValue.toString().trim());
                        currentValue.setLength(0);
                        continue;
                    }
                    currentValue.append(c);
                }
                values.add(currentValue.toString().trim());

                Map<String, Object> row = new LinkedHashMap<>();
                for (int i = 0; i < keys.length; i++) {
                    String key = keys[i].replaceAll("`", "").trim();
                    String value = values.get(i);

                    // 处理不同类型数据
                    Object formattedValue;
                    if (value.equalsIgnoreCase("NULL")) {
                        formattedValue = null;
                    } else if (value.matches("^\\d+$")) {  // 整数
                        formattedValue = Long.parseLong(value);
                    } else if (value.matches("^\\d+\\.\\d+$")) {  // 浮点数
                        formattedValue = Double.parseDouble(value);
                    } else {  // 字符串
                        formattedValue = value.replaceAll("^'(.*)'$", "$1");
                    }

                    row.put(key, formattedValue);
                }
                data.add(row);
            }

            DynamicEntity entity = new DynamicEntity();
            entity.setColumns(columns);
            entity.setData(data);


            return Result.ok().data("entity", entity);

        } catch (NoSuchFileException e) {
            return Result.error().message("SQL文件不存在");
        } catch (IOException e) {
            return Result.error().message("文件读取失败");
        } catch (Exception e) {
            e.printStackTrace();
            return Result.error().message("SQL解析错误: " + e.getMessage());
        }
    }
    @PostMapping("/setdbdetails")// 显示数据库具体信息
    public Result setdbdetails(String setid) {
        Subsetdb set=sm.selectById(setid);
        int DBid=set.getDbid();
        LambdaQueryWrapper<Db> lqw = new LambdaQueryWrapper<>();
        lqw.eq(Db::getDbid,DBid);
        Db db=dm.selectOne(lqw);
        String dbname=db.getDbname();// 数据库名称，这里其实就是表的名称
        LambdaQueryWrapper<attributessets> lqwst = new LambdaQueryWrapper<>();
        lqwst.eq(attributessets::getDbid,DBid);
        List<attributessets> at=atm.selectList(lqwst);// 属性集
        LambdaQueryWrapper<functionaldependencies> lqwfd = new LambdaQueryWrapper<>();
        lqwfd.eq(functionaldependencies::getDbid,DBid);
        List<functionaldependencies> fd = fdm.selectList(lqwfd);// 函数依赖集
        System.out.println("奖学金");


        return Result.ok().data("dbname",dbname).data("at",at).data("fd",fd);
    }
    @PostMapping("/setshowinstance")
    public Result setshowInstance(String setid) {
        Subsetdb set=sm.selectById(setid);
        int DBid=set.getDbid();
        String filePath = "E:/SQL_test/" + DBid + ".sql";
        try {
            // 读取SQL文件内容
            String sqlContent = new String(Files.readAllBytes(Paths.get(filePath)), StandardCharsets.UTF_8);

            // 使用SQL解析工具获取表结构
            List<TableSchema> schemas = SqlParserUtil.extractAllSchemas(sqlContent);
            if (schemas.isEmpty()) {
                return Result.error().message("未找到表结构信息");
            }

            // 使用Druid解析器获取完整列信息
            List<Map<String, String>> columns = new ArrayList<>();
            SQLStatementParser parser = new MySqlStatementParser(sqlContent);
            List<SQLStatement> statements = parser.parseStatementList();

            for (SQLStatement stmt : statements) {
                if (stmt instanceof SQLCreateTableStatement) {
                    SQLCreateTableStatement createStmt = (SQLCreateTableStatement) stmt;
                    for (SQLTableElement element : createStmt.getTableElementList()) {
                        if (element instanceof SQLColumnDefinition) {
                            SQLColumnDefinition colDef = (SQLColumnDefinition) element;
                            Map<String, String> columnInfo = new LinkedHashMap<>();
                            // 获取列名
                            String colName = colDef.getName().getSimpleName();
                            // 获取列类型（包含长度信息）
                            String colType = colDef.getDataType().getName();
                            if (colDef.getDataType().getArguments() != null) {
                                colType += colDef.getDataType().getArguments().toString()
                                        .replace("[", "(")
                                        .replace("]", ")")
                                        .replace(", ", ",");
                            }

                            columnInfo.put("name", colName);
                            columnInfo.put("type", colType);
                            columns.add(columnInfo);
                        }
                    }
                    break; // 只处理第一个表
                }
            }

            // 解析插入数据
            List<Map<String, Object>> data = new ArrayList<>();
            Pattern insertPattern = Pattern.compile(
                    "INSERT INTO\\s+`?(\\w+)`?\\s*\\((.*?)\\)\\s*VALUES\\s*(\\(.*?\\));",
                    Pattern.CASE_INSENSITIVE | Pattern.DOTALL
            );

            Matcher matcher = insertPattern.matcher(sqlContent);
            while (matcher.find()) {
                String[] keys = matcher.group(2).split("\\s*,\\s*");
                String valuesStr = matcher.group(3).replaceAll("[()]", "");

                List<String> values = new ArrayList<>();
                boolean inQuotes = false;
                StringBuilder currentValue = new StringBuilder();

                for (char c : valuesStr.toCharArray()) {
                    if (c == '\'') {
                        inQuotes = !inQuotes;
                    } else if (c == ',' && !inQuotes) {
                        values.add(currentValue.toString().trim());
                        currentValue.setLength(0);
                        continue;
                    }
                    currentValue.append(c);
                }
                values.add(currentValue.toString().trim());

                Map<String, Object> row = new LinkedHashMap<>();
                for (int i = 0; i < keys.length; i++) {
                    String key = keys[i].replaceAll("`", "").trim();
                    String value = values.get(i);

                    // 处理不同类型数据
                    Object formattedValue;
                    if (value.equalsIgnoreCase("NULL")) {
                        formattedValue = null;
                    } else if (value.matches("^\\d+$")) {  // 整数
                        formattedValue = Long.parseLong(value);
                    } else if (value.matches("^\\d+\\.\\d+$")) {  // 浮点数
                        formattedValue = Double.parseDouble(value);
                    } else {  // 字符串
                        formattedValue = value.replaceAll("^'(.*)'$", "$1");
                    }

                    row.put(key, formattedValue);
                }
                data.add(row);
            }

            DynamicEntity entity = new DynamicEntity();
            entity.setColumns(columns);
            entity.setData(data);


            return Result.ok().data("entity", entity);

        } catch (NoSuchFileException e) {
            return Result.error().message("SQL文件不存在");
        } catch (IOException e) {
            return Result.error().message("文件读取失败");
        } catch (Exception e) {
            e.printStackTrace();
            return Result.error().message("SQL解析错误: " + e.getMessage());
        }
    }

    @PostMapping("/setdbshowinstance")
    public Result setdbshowinstance(String setid) {
        Subsetdb set=sm.selectById(setid);
        int DBid=set.getSetdbid();
        String filePath = "E:/SQL_test/" + DBid + ".sql";
        try {
            // 读取SQL文件内容
            String sqlContent = new String(Files.readAllBytes(Paths.get(filePath)), StandardCharsets.UTF_8);

            // 使用SQL解析工具获取表结构
            List<TableSchema> schemas = SqlParserUtil.extractAllSchemas(sqlContent);
            if (schemas.isEmpty()) {
                return Result.error().message("未找到表结构信息");
            }

            // 使用Druid解析器获取完整列信息
            List<Map<String, String>> columns = new ArrayList<>();
            SQLStatementParser parser = new MySqlStatementParser(sqlContent);
            List<SQLStatement> statements = parser.parseStatementList();

            for (SQLStatement stmt : statements) {
                if (stmt instanceof SQLCreateTableStatement) {
                    SQLCreateTableStatement createStmt = (SQLCreateTableStatement) stmt;
                    for (SQLTableElement element : createStmt.getTableElementList()) {
                        if (element instanceof SQLColumnDefinition) {
                            SQLColumnDefinition colDef = (SQLColumnDefinition) element;
                            Map<String, String> columnInfo = new LinkedHashMap<>();
                            // 获取列名
                            String colName = colDef.getName().getSimpleName();
                            // 获取列类型（包含长度信息）
                            String colType = colDef.getDataType().getName();
                            if (colDef.getDataType().getArguments() != null) {
                                colType += colDef.getDataType().getArguments().toString()
                                        .replace("[", "(")
                                        .replace("]", ")")
                                        .replace(", ", ",");
                            }

                            columnInfo.put("name", colName);
                            columnInfo.put("type", colType);
                            columns.add(columnInfo);
                        }
                    }
                    break; // 只处理第一个表
                }
            }

            // 解析插入数据
            List<Map<String, Object>> data = new ArrayList<>();
            Pattern insertPattern = Pattern.compile(
                    "INSERT INTO\\s+`?(\\w+)`?\\s*\\((.*?)\\)\\s*VALUES\\s*(\\(.*?\\));",
                    Pattern.CASE_INSENSITIVE | Pattern.DOTALL
            );

            Matcher matcher = insertPattern.matcher(sqlContent);
            while (matcher.find()) {
                String[] keys = matcher.group(2).split("\\s*,\\s*");
                String valuesStr = matcher.group(3).replaceAll("[()]", "");

                List<String> values = new ArrayList<>();
                boolean inQuotes = false;
                StringBuilder currentValue = new StringBuilder();

                for (char c : valuesStr.toCharArray()) {
                    if (c == '\'') {
                        inQuotes = !inQuotes;
                    } else if (c == ',' && !inQuotes) {
                        values.add(currentValue.toString().trim());
                        currentValue.setLength(0);
                        continue;
                    }
                    currentValue.append(c);
                }
                values.add(currentValue.toString().trim());

                Map<String, Object> row = new LinkedHashMap<>();
                for (int i = 0; i < keys.length; i++) {
                    String key = keys[i].replaceAll("`", "").trim();
                    String value = values.get(i);

                    // 处理不同类型数据
                    Object formattedValue;
                    if (value.equalsIgnoreCase("NULL")) {
                        formattedValue = null;
                    } else if (value.matches("^\\d+$")) {  // 整数
                        formattedValue = Long.parseLong(value);
                    } else if (value.matches("^\\d+\\.\\d+$")) {  // 浮点数
                        formattedValue = Double.parseDouble(value);
                    } else {  // 字符串
                        formattedValue = value.replaceAll("^'(.*)'$", "$1");
                    }

                    row.put(key, formattedValue);
                }
                data.add(row);
            }

            DynamicEntity entity = new DynamicEntity();
            entity.setColumns(columns);
            entity.setData(data);


            return Result.ok().data("entity", entity);

        } catch (NoSuchFileException e) {
            return Result.error().message("SQL文件不存在");
        } catch (IOException e) {
            return Result.error().message("文件读取失败");
        } catch (Exception e) {
            e.printStackTrace();
            return Result.error().message("SQL解析错误: " + e.getMessage());
        }
    }
    // 新增值解析方法
    private List<String> parseValues(String valuesStr) {
        List<String> values = new ArrayList<>();
        boolean inQuotes = false;
        StringBuilder current = new StringBuilder();

        for (char c : valuesStr.toCharArray()) {
            if (c == '\'') {
                inQuotes = !inQuotes;
            } else if (c == ',' && !inQuotes) {
                values.add(current.toString().trim());
                current.setLength(0);
                continue;
            }
            current.append(c);
        }
        values.add(current.toString().trim());
        return values;
    }
    @PostMapping("/deletedb")
    public Result deletedb(String DBid) {// 删除数据库和本地存储的SQL文件
        // 删除数据库记录
        dm.deleteById(DBid);

        // 删除对应的SQL文件
        String filePath = "E:\\SQL_test\\" + DBid + ".sql";
        File sqlFile = new File(filePath);

        if (sqlFile.exists()) {
            if (sqlFile.delete()) {
                return Result.ok().message("数据库删除成功，且对应的SQL文件已删除");
            } else {
                return Result.error().message("数据库删除成功，但对应的SQL文件删除失败");
            }
        } else {
            return Result.ok().message("数据库删除成功，但对应的SQL文件不存在");
        }
    }
}
